
alter    trigger tfb_voucher_ai on dbo.tfb_voucher
instead of insert
as   
begin

  if @@rowcount=0 return
  set nocount on

  declare 
    @fyear integer,   
    @fmonth integer,
    @nvoucherno integer,
    @norderno integer,
    @nvouyearmonth varchar(6),
    @nfilorderno integer,
    @filcount integer,
    @voucherid         integer,
    @create_by       integer,
    @signer_by       integer,
    @vouwordid     integer,
    @orderno        integer,
    @voucherno         integer,
    @bdate       datetime,
    @update_time   datetime,
    @formerbno   integer,
    @formertype  integer,
    @formerbid   integer,
    @vouyearmonth  varchar(6),
    @check_by      integer,
    @binder_by       integer,
    @printtmpltid   integer,
    @filid         integer,
    @filorderno    integer,
    @affix integer,
    @bcarrypl integer
  
  declare @local_tab table(
    voucherid int primary key,
    bcode varchar(40),
    bdate datetime,
    bstate tinyint,
    bkind tinyint,
    create_by int,
    signer_by int,
    vouwordid int,
    orderno int,
    voucherno int,
    update_time datetime,
    formertype int,
    formerbid int,
    formerbno int,
    vouyearmonth varchar(6) ,
    check_by int,
    binder_by int,
    filid int,
    filorderno int,
    carforproloss int,
    affix int,
    create_time datetime,
    check_time datetime 
  )
  insert into @local_tab select * from inserted
  if(@@error<>0) return 

  select @filcount = count(fi_filid) from td_filiale
  declare tfb_voucher_cr cursor local forward_only static optimistic 
  for select voucherid,bdate,voucherno,orderno,vouwordid,voucherno,orderno,filid,filorderno
      from @local_tab where voucherid>0
  open tfb_voucher_cr
  fetch next from tfb_voucher_cr into @voucherid,@bdate,@voucherno,@orderno,@vouwordid
    ,@nvoucherno,@norderno,@filid,@filorderno
  while @@fetch_status = 0
  begin
    select @fyear=yearid,@fmonth=monthid from tfd_monthlycalendar where bdate<=@bdate and @bdate<=edate
    if @fyear is null
    begin
      set @fyear=year(@bdate)
      set @fmonth=month(@bdate)
    end
    set @nvouyearmonth=cast(@fyear as char(4))+ right(cast(100+@fmonth as char(3)),2)
    if (@voucherno = 0 or @orderno = 0 or @filorderno = 0) 
      exec p_fingetnewvouorderno @bdate,@filid,@vouwordid, @nvoucherno output, @norderno output,@nfilorderno output

    set @nvoucherno = isnull(@voucherno,0)
    set @norderno = isnull(@orderno,0)
    set @nfilorderno = isnull(@filorderno,0)
   
    if (exists(select voucherid from tfb_voucher   
      where voucherno=@nvoucherno and vouwordid=@vouwordid and vouyearmonth=@nvouyearmonth))    
    begin
      select @nvoucherno = max(voucherno)+1 from tfb_voucher   
        where vouwordid = @vouwordid and  vouyearmonth = @nvouyearmonth      
    end
    if (exists(select voucherid from tfb_voucher   
      where orderno = @norderno and vouyearmonth = @nvouyearmonth))
    begin    
      select @norderno = max(orderno)+1 from tfb_voucher 
        where vouyearmonth = @nvouyearmonth      
    end

    if (exists(select voucherid from tfb_voucher   
      where filorderno = @nfilorderno and vouyearmonth = @nvouyearmonth and filid=@filid) and @filcount>1)
    begin    
      select @nfilorderno = max(filorderno)+1 from tfb_voucher 
        where vouyearmonth = @nvouyearmonth and filid=@filid     
    end
    
    insert into tfb_voucher(
      voucherid,
      create_by,
      signer_by,
      vouwordid,
      orderno,
      voucherno,
      bdate,
      bcode,
      update_time,
      formerbno,
      formertype,
      formerbid,
      vouyearmonth,
      check_by,
      binder_by,
      filid,
      filorderno,
      affix,
      carforproloss,
      create_time
    ) select
      voucherid,
      create_by,
      signer_by,
      vouwordid,
      @norderno,
      @nvoucherno,
      bdate,
      bcode,
      update_time,
      formerbno,
      formertype,
      formerbid,
      @nvouyearmonth,
      check_by,
      binder_by,
      filid,
      @nfilorderno,
      affix,
      carforproloss,
      create_time
    from @local_tab new where new.voucherid=@voucherid

    fetch next from tfb_voucher_cr into @voucherid,@bdate,@voucherno,@orderno,@vouwordid
      ,@nvoucherno,@norderno,@filid,@filorderno
  end
  close tfb_voucher_cr
  deallocate tfb_voucher_cr


  insert into tfb_voucher 
  select * from @local_tab where voucherid<=0

end














